/*
* Copyrigth 2008 Company All rigths reserved
* File Name: TS_Message_CRUD.sql
* Created By:    Wenbin.Xu
* Created Date:   2013/7/8 16:26:25
* Description:    TS_Message的增删改查存储过程
* Change History: 
* 
* */

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create  PROCEDURE UP_TS_Message_Add
	@SendUser  bigint,
	@ReceiverUser  bigint,
	@MessageContent  varchar(-1),
	@MessageTitle  varchar(300),
	@SendDate  datetime
AS
SET NOCOUNT ON
BEGIN
	INSERT INTO TS_Message
    (
        SendUser,
        ReceiverUser,
        MessageContent,
        MessageTitle,
        SendDate
    )
	VALUES
    (
        @SendUser,
        @ReceiverUser,
        @MessageContent,
        @MessageTitle,
        @SendDate)
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Message_Delete
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	DELETE FROM  TS_Message
	WHERE
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Message_Update
	@Idx  bigint,
	@SendUser  bigint,
	@ReceiverUser  bigint,
	@MessageContent  varchar(-1),
	@MessageTitle  varchar(300),
	@SendDate  datetime
AS
SET NOCOUNT ON
BEGIN
	UPDATE TS_Message
    SET 
	SendUser=@SendUser,
	ReceiverUser=@ReceiverUser,
	MessageContent=@MessageContent,
	MessageTitle=@MessageTitle,
	SendDate=@SendDate
    WHERE
    Idx=@Idx
END






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Message_View
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	SELECT 
    Idx,
    SendUser,
    ReceiverUser,
    MessageContent,
    MessageTitle,
    SendDate
    FROM TS_Message with(nolock)
    WHERE 
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_Message_ListByPaging
(
    @pageIndex  INT,
    @pageSize   INT ,
    @recordCount INT=0 OUTPUT 
)
AS
BEGIN
	
	--calculate start index & end index
	DECLARE @startIndex  AS INT,
	        @endIndex    AS INT
	
	SELECT @startIndex = @pageSize * (@pageIndex -1) + 1,
	       @endIndex = @pageSize * @pageIndex
	
	DECLARE @sql    NVARCHAR(max)
	DECLARE @where  NVARCHAR(2000)
	SET @sql=''	
	SET @where = ''
    
	
	--calculate total record count
	SET @sql='SELECT @recordCount=COUNT(1) FROM TS_Message with(nolock) WHERE (1=1) ' + @where
	EXEC sp_executesql @sql, N'@recordCount int=0 OUTPUT' , @recordCount OUTPUT 
	
	
	SET @sql = 
	    'SELECT ROW_NUMBER () OVER (ORDER BY 
            Idx ASC
        ) RowId,
        Idx,
        SendUser,
        ReceiverUser,
        MessageContent,
        MessageTitle,
        SendDate
        FROM TS_Message with(nolock)
        WHERE (1=1) ' + @where
				
	SET @sql = 'SELECT T.* FROM (' 
	+ @sql
	+ ') T where T.RowId between @startIndex and @endIndex '

	exec sp_executesql @sql,N'@startIndex int, @endIndex int',@startIndex,@endIndex 
END